oracle 关联表更新的四种方法 | 您所在的位置:网站首页 › sql 根据查询结果更新信息的命令 › oracle 关联表更新的四种方法 |
两张表关联更新,更新数据来自另一张表。 创建数据表T1和T2并插入数据: CREATE TABLE T1( FNAME VARCHAR2(50), FMONEY NUMBER ); CREATE TABLE T2( FNAME VARCHAR2(50), FMONEY NUMBER );
INSERT INTO T1(FNAME,FMONEY)values ('A',20); INSERT INTO T1(FNAME,FMONEY)values ('B',30); INSERT INTO T2(FNAME,FMONEY)values ('A',100); INSERT INTO T2(FNAME,FMONEY)values ('C',20); INSERT INTO T2(FNAME,FMONEY)values ('D',10); 数据插入完成如下图所示: 现需求:参照T2表,修改T1表,修改条件为两表的fname列内容一致。
方法一:直接update更新 UPDATE T1 SET T1.FMONEY = (select T2.FMONEY from T2 where T2.FNAME = T1.FNAME); 更新后的T1表数据如下: 可以发现,A记录对应的值由原来的20被更新为100,B的值由原来的30被更新为空。这是因为B在T2表中没有被找到对应记录导致的。这里要特别注意,如果要求B对应的记录不被更改,这种写法是不适合的。
方法二:使用EXISTS更新 UPDATE T1 SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME) WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME); 更新后的T1表数据如下: 更新后A记录对应的值被更新为100,B记录对应的值没有被改动。这种方法解决了方法一中B的值被更新为空的问题。
方法三:内联视图更新 这种更新方式有一个前提条件,T2表的FNAME字段必须为主键。 先为T2表添加主键: ALTER TABLE T2 ADD CONSTRAINT pk_test2 PRIMARY KEY(FNAME); 然后执行更新语句: update ( select t1.fmoney fmoney1,t2.fmoney fmoney2 from t1,t2 where t1.fname = t2.fname )t set fmoney1 =fmoney2; 更新后的T1表数据如下: 可以看到这种方法执行结果跟方法二的执行结果一致,如果在T2表中根据FNAME匹配到的记录就更新,匹配不到的记录不做操作。
方法四:merge合并更新 merge into t1 using (select t2.fname,t2.fmoney from t2) t on (t.fname = t1.fname) when matched then update set t1.fmoney = t.fmoney; 更新后的T1表数据如下: 可以看到这种方法执行结果跟方法二的执行结果一致,如果在T2表中根据FNAME匹配到的记录就更新,匹配不到的记录不做操作。
oracle中的merge合并函数,使用的业务场景一般为:新增数据的时候要先查询数据库是否已经存在该条记录,存在就更新数据,不存在就新增数据。 改动前面的需求,如果T1表中的数据根据FNAME字段在T2表中存在,就更新T1表的FMONEY字段,如果T1表的数据根据FNAME字段没有在T2表中匹配到记录,就把T2表的记录插入到T1中,语句就可以这样写: merge into t1 using (select t2.fname,t2.fmoney from t2) t on (t.fname = t1.fname) when matched then update set t1.fmoney = t.fmoney when not matched then insert(fname,fmoney)values(t.fname,t.fmoney); 更新后的T1表数据如下: T2中FNAME为A的记录的FMONEY为100,所以T1表中FNAME为A的记录的FMONEY字段被更新为100。 T1表FNAME为B的记录没有在T2表中找到,所以T1中FNAME为B的记录没有被更新。 T2表FNAME为C和D的记录,在T1表不存在,所以执行了insert的操作,将T2表中FNAME为C和D的记录插入到T1表。
|
CopyRight 2018-2019 实验室设备网 版权所有 |